﻿if exists (select * from sys.objects where type='U' and name like 'varrestrictions')
	begin
		drop table varrestrictions
	end

if exists (select * from sys.objects where type='U' and name like 'restrictions')
	begin
		drop table restrictions
	end
if exists (select * from sys.objects where type='U' and name like 'ofunction')
	begin
		drop table ofunction
	end
if exists (select * from sys.objects where type='U' and name like 'mathmodel')
	begin
		drop table mathmodel
	end

create table mathmodel
(
	mm_idmathmodel int not null,
	mm_name varchar(100) not null,
	mm_description varchar(200) not null,
	mm_option bit,
	mm_status bit,
	 CONSTRAINT un_mathmodel UNIQUE NONCLUSTERED 
	(
		mm_name ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
	CONSTRAINT [pk_mathmodel] PRIMARY KEY CLUSTERED 
	(
		mm_idmathmodel ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

create table ofunction
(
	of_idmathmodel int not null,
	of_idofunction int not null,
	of_variable varchar(2) not null,
	of_value float not null,
	of_lowerlimit float,
	of_upperlimit float,
	CONSTRAINT [pk_ofunction] PRIMARY KEY CLUSTERED 
	(
		of_idmathmodel ASC,
		of_idofunction asc
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ALTER TABLE [dbo].ofunction  WITH CHECK ADD  CONSTRAINT [FK_ofunction_mathmodel] FOREIGN KEY(of_idmathmodel)
REFERENCES [dbo].mathmodel (mm_idmathmodel)
GO
ALTER TABLE [dbo].ofunction CHECK CONSTRAINT [FK_ofunction_mathmodel]
GO


create table restrictions
(
	rs_idmathmodel int not null,
	rs_idrestrictions int not null,
	rs_type bit,
	rs_value float not null,
	CONSTRAINT [pk_restrictions] PRIMARY KEY CLUSTERED 
	(
		rs_idmathmodel ASC,
		rs_idrestrictions asc
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ALTER TABLE [dbo].restrictions  WITH CHECK ADD  CONSTRAINT [FK_restrictions_mathmodel] FOREIGN KEY(rs_idmathmodel)
REFERENCES [dbo].mathmodel (mm_idmathmodel)
GO
ALTER TABLE [dbo].restrictions CHECK CONSTRAINT [FK_restrictions_mathmodel]
GO


create table varrestrictions
(
	vr_idmathmodel int not null,
	vr_idrestrictions int not null,
	vr_idofunction int not null,
	vr_value float not null,
	CONSTRAINT [pk_varrestrictions] PRIMARY KEY CLUSTERED 
	(
		vr_idmathmodel ASC,
		vr_idrestrictions asc,
		vr_idofunction asc
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ALTER TABLE [dbo].varrestrictions  WITH CHECK ADD  CONSTRAINT [FK_varrestrictions_restrictions] FOREIGN KEY(vr_idmathmodel,vr_idrestrictions)
REFERENCES [dbo].restrictions (rs_idmathmodel,rs_idrestrictions)
GO
ALTER TABLE [dbo].varrestrictions CHECK CONSTRAINT [FK_varrestrictions_restrictions]
GO
ALTER TABLE [dbo].varrestrictions  WITH CHECK ADD  CONSTRAINT [FK_varrestrictions_ofunction] FOREIGN KEY(vr_idmathmodel,vr_idofunction)
REFERENCES [dbo].ofunction (of_idmathmodel,of_idofunction)
GO
ALTER TABLE [dbo].varrestrictions CHECK CONSTRAINT [FK_varrestrictions_ofunction]
GO